package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.StudentForm;

public class UserModel {
	
	private static String updateUserStatus = "UPDATE users SET student_id = ?" +
						"WHERE user_name = ?";
	private static String selectUser = "SELECT * FROM users WHERE user_name = ?";
	private static String selectUserRole = "SELECT * FROM user_roles WHERE user_name = ?";
	private static String selectUserbyEmail = "SELECT * FROM users WHERE email = ?";
	private static String insertUser = "INSERT INTO users " + 
						"(user_name, password, email) VALUES " +
						"(?, md5(?), ?)";
	private static String insertUserRole = "INSERT INTO user_roles " +
						"(user_name, role) VALUES (?, ?)";
	
	public static void insertUser(String user, String password, String email) throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			
			// Setup Query
			PreparedStatement pstmt = conn.prepareStatement(insertUser);
			pstmt.setString(1, user);
			pstmt.setString(2, password);
			pstmt.setString(3, email);
			
			// Execute prepared statement
			pstmt.executeUpdate();

			// Update user role table
			insertUserRole(conn, user, "student");
			
			conn.commit();
			conn.setAutoCommit(true);
			
			pstmt.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	private static void insertUserRole (Connection conn, String user, String role) throws DbException {
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(insertUserRole);
			pstmt.setString(1, user);
			pstmt.setString(2, role);
			
			// Execute prepared statement
			pstmt.executeUpdate();
			
			pstmt.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void updateUserStatus (Connection conn, String user, int app_id) throws DbException {
		try {
			
			// Setup Query
			PreparedStatement pstmt = conn.prepareStatement(updateUserStatus);
			pstmt.setInt(1, app_id);
			pstmt.setString(2, user);
			
			// Execute Query
			pstmt.executeUpdate();

			pstmt.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} 
	}
	
	public static CachedRowSet getUser(String user) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Setup Query
			PreparedStatement pstmt = conn.prepareStatement(selectUser);
			pstmt.setString(1, user);
			
			// Execute Query
			ResultSet rset = pstmt.executeQuery();

			CachedRowSet crset = new CachedRowSetImpl();
			crset.populate(rset);

			rset.close();
			pstmt.close();
			conn.close();

			return crset;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getUserRole(String user) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Setup Query
			PreparedStatement pstmt = conn.prepareStatement(selectUserRole);
			pstmt.setString(1, user);
			
			// Execute Query
			ResultSet rset = pstmt.executeQuery();

			CachedRowSet crset = new CachedRowSetImpl();
			crset.populate(rset);

			rset.close();
			pstmt.close();
			conn.close();

			return crset;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getUserbyEmail(String email) 
		throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Setup Query
			PreparedStatement pstmt = conn.prepareStatement(selectUserbyEmail);
			pstmt.setString(1, email);
			
			// Execute Query
			ResultSet rset = pstmt.executeQuery();

			CachedRowSet crset = new CachedRowSetImpl();
			crset.populate(rset);

			rset.close();
			pstmt.close();
			conn.close();

			return crset;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}
